﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using System.Data.SqlClient;
using System.IO;
using Drawing=System.Drawing;
using Microsoft.Office.Interop.Excel;
using Microsoft.Office.Interop;
using System.Drawing;

namespace SY_Anekay_LMJ_OutPutExcelReport
{
    public class OutPutExcelReportTool
    {
        private const int _excelVersionBefore03 = -4143;
        private const int _excelVersionAfter03 = 56;
        public  DataSet DataSet;
        public Workbook Workbook { get ; set; }
        private Application excelApp;
        public string WorkOrder { get; set; }

        public string ConnectString { get; set; }
       
        //Execel2007orNewer
        int excelFormatNum { get { return Convert.ToDouble(excelVersion) < 12 ? _excelVersionBefore03 : _excelVersionAfter03; } }
        
        string excelVersion;

        public OutPutExcelReportTool(string databaseServer,string database, string loginName,string loginPassword,string workOrder)
        {
            ConnectString= $"data source={databaseServer};initial catalog={database};user id={loginName};pwd={loginPassword}";
            DataSet = ConnectDatabase(workOrder);
        }

        public OutPutExcelReportTool(){}

        public void InitialWoroBook()
        {
            excelApp = new Application();
            excelVersion = excelApp.Version;
            Workbooks workbooks = excelApp.Workbooks;
            Workbook workbook = workbooks.Add(XlWBATemplate.xlWBATWorksheet);
            Workbook = workbook;
        }

        //C:\Kingdee\WebSite\tempfilePath\ D:\program\kingdee\WebSite\tempfilePath
        public string OutPut(string fileAdressPath= @"D:\program\kingdee\WebSite\tempfilePath\")
        {
            string filePath = fileAdressPath;
            string fileName= DateTime.Now.Date.ToString("yyyyMMdd")+new Random().Next(1000)+".xlsx";
            filePath += fileName;
           
            Workbook workbook = Workbook;
            try
            {
                if (File.Exists(filePath))
                {
                    try
                    {
                        File.Delete(filePath);
                    }
                    catch (IOException e)
                    {
                        Console.WriteLine(e.Message);
                    }
                }
                workbook.SaveAs(filePath);
                excelApp.Visible = false;
                return fileName;
            }
            finally
            {
                excelApp.Workbooks.Close();
                excelApp.Quit();
                System.Runtime.InteropServices.Marshal.ReleaseComObject(excelApp);
                GC.Collect();
            }
            
        }
        
        public void CreateWorkReport(string flashBefore="",string flashAfter="")
        {
            Workbook workbook = Workbook;
            System.Data.DataTable dt = DataSet.Tables["BINEntryData"];
            var a= dt.AsEnumerable().GroupBy(p => Convert.ToInt32( p.Field<string>("ExamineType"))).Where(p=> p.Key> 0 & p.Key < 6).Select(p=>(ExamineType)(p.Key) ).ToList();
            a.Sort(new ExamineTypeCompare());
            FillProduceSheetTool sheetTool = new FillProduceSheetTool(workbook, DataSet,flashBefore,flashAfter);

            //excel填充方法以序号1为第一页，实际使用时要注意序号需+1，由于思源的下粹报表固定在第一页，所以测试日报表第一页为0+2=第二页
            for (int i = 0; i < a.Count(); i++)
            {
                sheetTool.FillExamineSheet(a[i], i + 2);
            }
            sheetTool.FillSummarySheet(a.Count + 2, a);
            
        }

        public void CreateProductivity(int startMonth,int endMonth,int year)
        {
            Workbook workbook = Workbook;            
            FillProdutivitySheetTool sheetTool = new FillProdutivitySheetTool(workbook, DataSet);
            sheetTool.FillSheet(startMonth, endMonth, year);
            excelApp.DisplayAlerts = false;
            ((Worksheet)workbook.Worksheets[1]).Delete();
            excelApp.DisplayAlerts = true;
        }

        [Obsolete]
        public void OutPut(string filePath,bool useless)
        {
            //Application excelApp = new Application();
            try
            {

                //excelVersion = excelApp.Version;
                //Workbooks workbooks = excelApp.Workbooks;
                Workbook workbook = Workbook;
                

                FillProduceSheetTool sheetTool = new FillProduceSheetTool(workbook, DataSet);
                sheetTool.FillExamineSheet(ExamineType.BIN, 1);

                if (File.Exists(filePath))
                {
                    try
                    {
                        File.Delete(filePath);
                    }
                    catch (IOException e)
                    {
                        Console.WriteLine(e.Message);
                    }
                }

                workbook.SaveAs(filePath);
                excelApp.Visible = false;
            }
            finally
            {
                excelApp.Workbooks.Close();
                excelApp.Quit();
                System.Runtime.InteropServices.Marshal.ReleaseComObject(excelApp);
                GC.Collect();
            }

        }

        public void OutPutAbandon(DataSet dataSet,string filePath)
        {
            Application excelApp = new Application();
            excelVersion = excelApp.Version;
            Workbooks workbooks = excelApp.Workbooks;
            Workbook workbook = workbooks.Add(XlWBATemplate.xlWBATWorksheet);
            Worksheet worksheetOfBIN = (Worksheet)workbook.Worksheets[1];
            worksheetOfBIN.Name = "直接测分BIN";

            string[] BIN = new string[] {"解前1B",
"解前1P",
"解前2B",
"解前2P",
"解前3",
"解前4",
"解前5",
"解后1B",
"解后1P",
"解后2B",
"解后2P",
"解后3",
"解后4",
"解后5",
"EF(无ID)",
"ES",
"破损"};

            List<Cell> headKeyCell = new List<Cell>
            {
                new Cell(1,1,"型号"),
                new Cell(1,2,"单号"),
                new Cell(1,3,"原始单号"),
                new Cell(1,4,"测试方案"),
                new Cell(3,1,"光罩号码"),
                new Cell(3,2,"整单良率"),
                new Cell(3,3,"Flash型号解前"),
                new Cell(3,4,"Flash型号解后"),
                new Cell(3,5,"ID解前"),
                new Cell(3,5,"ID解后"),
                new Cell(5,1,"来料数量"),
                new Cell(5,2,"下粹数量"),
                new Cell(5,3,"已测试数量"),
                new Cell(5,4,"未测试数量"),
                new Cell(5,5,"参数设置"),
            };

            foreach (var cell in headKeyCell)
            {
                cell.Y += 2;
                cell.X += 3;
                cell.IsBold = true;
                cell.BackColor = Color.CornflowerBlue;
                cell.FontColor = Color.White;
                cell.BorderColor = Color.LightSteelBlue;
                cell.Width = 20M;
                
            }
            Dictionary<string, string> translateCHNToEng = new Dictionary<string, string> {
               {"型号","Model"},
{"单号","WorkOrder"},
{"原始单号","OriginBillNo"},
{"测试方案","TestPlan"},
{"光罩号码","MaskNumber"},
{"整单良率","EmptyGoodRate"},
{"Flash型号解前","FlashBefore"},
{"Flash型号解后","FlashAfter"},
{"ID解前","IdBefore"},
{"ID解后","IdAfter"},
{"来料数量","IncomingQTY"},
{"下粹数量","DishesQTY"},
{"已测试数量","EmptyTestedQty"},
{"未测试数量","EmptyTestingQty"},
{"参数设置","ParameterSetting"}
            };

            DataRow rowHeadValue = DataSet.Tables?["BINHeadData"]?.Rows?[0];
            List<Cell> headValueCell = new List<Cell>();
            if (rowHeadValue != null)
            {
                foreach (var cell in headKeyCell)
                {
                    headValueCell.Add(new Cell(cell.X + 1, cell.Y, rowHeadValue[translateCHNToEng[cell.Content]].ToString()) { BorderColor = Color.LightSteelBlue ,Width=20});
                }
            }


            //4row,and2row empty,

            int subHeadStartY = headKeyCell.Max(p => p.Y)+3;
            int subHeadStartX = 3;

            List<Cell> subHeadCell = new List<Cell>
            {
                new Cell(subHeadStartX,subHeadStartY,rowHeadValue["TestPlan"]+" 解码前"),
                new Cell(subHeadStartX+7,subHeadStartY,rowHeadValue["TestPlan"]+" 解码后"),
                new Cell(subHeadStartX,subHeadStartY+1,rowHeadValue["FlashBefore"].ToString()),
                new Cell(subHeadStartX+7,subHeadStartY+1,rowHeadValue["FlashAfter"].ToString()),
                new Cell(subHeadStartX,subHeadStartY+2,"BIN1"),
                new Cell(subHeadStartX+1,subHeadStartY+2,"BIN1"),
                new Cell(subHeadStartX+2,subHeadStartY+2,"BIN2"),
                new Cell(subHeadStartX+3,subHeadStartY+2,"BIN2"),
                new Cell(subHeadStartX+4,subHeadStartY+2,"BIN3"),
                new Cell(subHeadStartX+5,subHeadStartY+2,"BIN4"),
                new Cell(subHeadStartX+6,subHeadStartY+2,"BIN5"),
                new Cell(subHeadStartX+7,subHeadStartY+2,"BIN1"),
                new Cell(subHeadStartX+8,subHeadStartY+2,"BIN1"),
                new Cell(subHeadStartX+9,subHeadStartY+2,"BIN2"),
                new Cell(subHeadStartX+10,subHeadStartY+2,"BIN2"),
                new Cell(subHeadStartX+11,subHeadStartY+2,"BIN3"),
                new Cell(subHeadStartX+12,subHeadStartY+2,"BIN4"),
                new Cell(subHeadStartX+13,subHeadStartY+2,"BIN5"),
                new Cell(subHeadStartX+14,subHeadStartY+2,"EF(无ID)"),
                new Cell(subHeadStartX+15,subHeadStartY+2,"ES(解码NG)"),
                new Cell(subHeadStartX+16,subHeadStartY+2,"破损"),
                new Cell(subHeadStartX+17,subHeadStartY+2,"TOTAL"),
            };
            foreach (var cell in subHeadCell)
            {
                cell.FontColor = Color.White;
                cell.BackColor = Color.CornflowerBlue;
            }

            int entryDataX = 1;
            int entryDataY = subHeadStartY + 2 + 3;

            List<Cell> entryTitleCell = new List<Cell>();
            List<Cell> entryContentCell = new List<Cell>();
            List<Cell> entrySubSumCell = new List<Cell>();
            List<Cell> entrySubSumPercentCell = new List<Cell>();
            List<Cell> entrySumCell = new List<Cell>();
            List<Cell> entrySumPercentCell = new List<Cell>();

            System.Data.DataTable entryDataTable = DataSet.Tables["BINEntryData"];
            List<BINEntryElement> binElement = new List<BINEntryElement>();

            foreach (System.Data.DataRow row in entryDataTable.Rows)
            {                
                binElement.Add(new BINEntryElement(Convert.ToDecimal(row["Thickness"]), Convert.ToDecimal(row["QTY"]), row["MaterialClass"].ToString(), row["Group"].ToString(), Convert.ToDateTime(row["DateTime"]), row["ExamineType"].ToString()));
            }

            var entryGroup = from BINEntryElement t1 in binElement
                             where t1.ExamineType == "1"
                             orderby t1.Thickness, t1.Date, t1.Group
                             group new { t1.Thickness, t1.Date,t1.Group, } by new { t1.Thickness,  t1.Date,t1.Group } into t2
                             select new { t2.Key.Thickness, t2.Key.Date, t2.Key.Group };

            //EntryGroupingInformation,Set thickness whlie thickness value change
            decimal thicknessTemp = 0;

            string group = "";
            DateTime date = DateTime.MinValue;
            bool isFirstRow = true;
            int rowIndexOfSumStart = 0;
            int rowIndexOfSumEnd = 0;
            
            foreach (var item in entryGroup)
            {
                
                entryDataX = 1;

                if (item.Thickness != thicknessTemp)
                {
                    rowIndexOfSumStart = isFirstRow ? entryDataY + 1 : rowIndexOfSumStart;
                    if (!isFirstRow)
                    {
                        entrySubSumCell.AddRange(GetSubSumCell(ref entryTitleCell, entryDataY, rowIndexOfSumStart, rowIndexOfSumEnd, Convert.ToChar("C"), Convert.ToChar("T")));
                        entryDataY += 1;
                        entrySubSumPercentCell.AddRange(GetPercentCell(ref entryTitleCell, entryDataY, Convert.ToChar("C"), Convert.ToChar("T")));
                        entryDataY += 1;
                        rowIndexOfSumStart = entryDataY + 1;
                    }
                    

                    thicknessTemp = item.Thickness;
                    entryTitleCell.Add(new Cell(entryDataX, entryDataY, Math.Round(thicknessTemp, 4).ToString() + "mm", true));
                    entryDataY += 1;
                    isFirstRow = false;
                }               

                entryTitleCell.Add(new Cell(entryDataX, entryDataY, item.Date.ToShortDateString().ToString()));
                date = item.Date;
                entryDataX += 1;
                entryTitleCell.Add(new Cell(entryDataX, entryDataY, item.Group));
                entryDataX += 1;
                group = item.Group;
                foreach (var type in BIN)
                {
                    entryContentCell.Add(new Cell(entryDataX, entryDataY, Convert.ToDecimal(binElement.Where(p => p.Thickness == thicknessTemp && p.Date == date && p.Group == group && p.MaterialClass == type && p.ExamineType == "1").Select(q => q.Qty)?.FirstOrDefault()).ToString("0.##")));
                    entryDataX += 1;
                }
                
                entryContentCell.Add(new Cell(entryDataX, entryDataY,$"=SUM(C{entryDataY}:S{entryDataY}" ));
                entryDataY += 1;
                rowIndexOfSumEnd = entryDataY-1;
            }

            List<Cell> lastSubSumRow = GetSubSumCell(ref entryTitleCell, entryDataY, rowIndexOfSumStart, rowIndexOfSumStart, Convert.ToChar("C"), Convert.ToChar("T"));
            entrySubSumCell.AddRange(lastSubSumRow);
            entryDataY += 1;
            List<Cell> lastSubSumPercentRow = GetPercentCell(ref entryTitleCell, entryDataY, Convert.ToChar("C"), Convert.ToChar("T"));            
            entryDataY += 1;
            entrySubSumPercentCell.AddRange(lastSubSumPercentRow);

            entryTitleCell.Add(new Cell(1, entryDataY, "合计", true));
            entryDataY += 1;
            entrySumCell.AddRange(GetSumCell(ref entryTitleCell, entryDataY, entryContentCell.Select(p => p.Y).Distinct(), Convert.ToChar("C"), Convert.ToChar("T")));
            entryDataY += 1;
            entrySumPercentCell.AddRange(GetPercentCell(ref entryTitleCell, entryDataY, Convert.ToChar("C"), Convert.ToChar("T")));



            Range headFrame = worksheetOfBIN.Range[worksheetOfBIN.Cells[headKeyCell.Min(p => p.Y) - 1, headKeyCell.Min(p => p.X) - 1],
               worksheetOfBIN.Cells[headValueCell.Max(p => p.Y) + 1, headValueCell.Max(p => p.X) + 1]];
            headFrame.Interior.Color = Drawing.Color.LightSteelBlue;
            headFrame.Borders[XlBordersIndex.xlEdgeTop].Color = Color.RoyalBlue;
            headFrame.Borders[XlBordersIndex.xlEdgeBottom].Color = Color.RoyalBlue;
            headFrame.Borders[XlBordersIndex.xlEdgeLeft].Color = Color.RoyalBlue;
            headFrame.Borders[XlBordersIndex.xlEdgeRight].Color = Color.RoyalBlue;

            List < Cell > cellGatherCollection = new List<Cell>();
            cellGatherCollection.AddRange(headKeyCell);
            cellGatherCollection.AddRange(headValueCell);
            cellGatherCollection.AddRange(subHeadCell);
            cellGatherCollection.AddRange(entryContentCell);
            cellGatherCollection.AddRange(entryTitleCell);
            cellGatherCollection.AddRange(entrySubSumCell);
            cellGatherCollection.AddRange(entrySubSumPercentCell);
            cellGatherCollection.AddRange(entrySumCell);
            cellGatherCollection.AddRange(entrySumPercentCell);            

            foreach (var cell in cellGatherCollection)
            {
                worksheetOfBIN.Cells[cell.Y, cell.X] = cell.Content;
                Range range = worksheetOfBIN.Range[worksheetOfBIN.Cells[cell.Y, cell.X], worksheetOfBIN.Cells[cell.Y, cell.X]];
                range.Interior.Color = cell.BackColor;
                range.Font.Color = cell.FontColor;
                range.Font.Bold = cell.IsBold;
                range.HorizontalAlignment = XlHAlign.xlHAlignCenter;
                range.Borders.Color = cell.BorderColor;
                range.ColumnWidth = cell.Width == 0 ? range.ColumnWidth : cell.Width;
                range.NumberFormat = cell.NumberFormat ?? range.NumberFormat ;
            }

            


            if (File.Exists(filePath))
            {
                try
                {
                    File.Delete(filePath);
                }
                catch (IOException e)
                {
                    Console.WriteLine(e.Message);
                }
            }

            workbook.SaveAs(filePath);
            excelApp.Visible = true;

            
        }

        public DataSet ConnectDatabase(string workOrder)
        {
            SqlConnection connection = new SqlConnection(ConnectString);           
            DataSet data = new DataSet();
            connection.Open();
            SqlDataAdapter adapter = new SqlDataAdapter($"Select * from V_SY_BINHeadData where WorkOrder='{workOrder}'", connection);
            adapter.Fill(data, "BINHeadData");
            adapter = new SqlDataAdapter($"Select * from V_SY_BINSubTitle where WorkOrder='{workOrder}'", connection);
            adapter.Fill(data, "BINSubTitle");
            adapter = new SqlDataAdapter($"Select * from V_SY_BINEntryData where WorkOrder='{workOrder}'", connection);
            adapter.Fill(data, "BINEntryData");
            connection.Close();
            connection.Dispose();
            return data;
        }
        public void ConnectDatabaseForTest(string workOrder)
        {

            SqlConnection connection = new SqlConnection(ConnectString);
            DataSet data = new DataSet();
            connection.Open();
            SqlDataAdapter adapter = new SqlDataAdapter($"Select top 1 * from V_SY_BINHeadData where WorkOrder='{workOrder}'", connection);
            adapter.Fill(data, "BINHeadData");
            adapter = new SqlDataAdapter($"Select top 1 * from V_SY_BINSubTitle where WorkOrder='{workOrder}'", connection);
            adapter.Fill(data, "BINSubTitle");
            adapter = new SqlDataAdapter($"Select * from FROANKTEST where WorkOrder='{workOrder}'", connection);
            adapter.Fill(data, "BINEntryData");
            adapter = new SqlDataAdapter($"SELECT top 1 * FROM v_sy_capacityqty WHERE workorder='{workOrder}'", connection);
            adapter.Fill(data, "CapacityQty");
            adapter = new SqlDataAdapter($"SELECT * FROM v_SY_fortestExamineSummaryEntry WHERE workorder='{workOrder}'", connection);
            adapter.Fill(data, "SummaryEntry");
            connection.Close();
            connection.Dispose();
            DataSet=data;
        }
        

        public List<Cell> GetSubSumCell(ref List<Cell> entryTitleCell, int yCoordinate,int rowIndexOfSumStart, int rowIndexOfSumEnd,Char startXcoordinate,Char endXCoordinate)
        {
            List<Cell> sumCellList = new List<Cell>();
            if (char.IsLetter(startXcoordinate) & char.IsLetter(endXCoordinate))
            {
                entryTitleCell.Add(new Cell(1, yCoordinate, "Q'ty"));
                Char start=startXcoordinate;
                Char end = endXCoordinate;
                for (int i = Convert.ToInt32(start); i <= Convert.ToInt32(end); i++)
                {
                    char letter = Convert.ToChar(i);
                    sumCellList.Add(new Cell(i-64, yCoordinate, $"=SUM({letter.ToString().ToUpper()}{rowIndexOfSumStart.ToString()}:{letter.ToString().ToUpper()}{rowIndexOfSumEnd.ToString()}"));
                }

                return sumCellList;
            }
            else
                throw new Exception($"Incorrect parameter startXcoordinate:[{startXcoordinate}] or endXCoordinate:[{endXCoordinate}],require letter");
        }

        /// <summary>
        /// Common use for summary and subsummary
        /// </summary>
        /// <param name="entryTitleCell"></param>
        /// <param name="yCoordinate"></param>
        /// <param name="startXcoordinate"></param>
        /// <param name="endXCoordinate"></param>
        /// <returns></returns>
        public List<Cell> GetPercentCell(ref List<Cell> entryTitleCell,int yCoordinate, Char startXcoordinate, Char endXCoordinate)
        {
            List<Cell> sumPercentCell = new List<Cell>();
            if (char.IsLetter(startXcoordinate) & char.IsLetter(endXCoordinate))
            {
                entryTitleCell.Add(new Cell(1, yCoordinate, "Q'ty%"));
                Char start = startXcoordinate;
                Char end = endXCoordinate;
                for (int i = Convert.ToInt32(start); i <= Convert.ToInt32(end); i++)
                {
                    Char column = Convert.ToChar(i);
                    sumPercentCell.Add(new Cell(i-64, yCoordinate, $"={column.ToString().ToUpper()}{yCoordinate - 1}/{"T"}{yCoordinate - 1}") { NumberFormat = "0.000%" });
                }
                return sumPercentCell;
            }
            else
                throw new Exception($"Incorrect parameter startXcoordinate:[{startXcoordinate}] or endXCoordinate:[{endXCoordinate}],require letter");

        }

        public List<Cell> GetSumCell(ref List<Cell> entryTitleCell, int yCoordinate,IEnumerable<int>entryContentYCoordinateCollection, Char startXcoordinate, Char endXCoordinate)
        {
            List<Cell> SumCell = new List<Cell>();
            if (char.IsLetter(startXcoordinate) & char.IsLetter(endXCoordinate))
            {
                entryTitleCell.Add(new Cell(1, yCoordinate, "Q'ty"));
                var entryCellYPoint = entryContentYCoordinateCollection.Distinct();
                Char start = startXcoordinate;
                Char end = endXCoordinate;
                for (int i = Convert.ToInt32(start); i <= Convert.ToInt32(end); i++)
                {
                    char column = Convert.ToChar(i);
                    StringBuilder elementPlusString = new StringBuilder("=");
                    foreach (var YPoint in entryCellYPoint)
                    {
                        elementPlusString.Append(column.ToString().ToUpper() + YPoint.ToString() + "+");
                    }
                    elementPlusString.Remove(elementPlusString.Length - 1, 1);
                    SumCell.Add(new Cell(i-64, yCoordinate, $"{elementPlusString}"));
                }
                return SumCell;
            }
            else
                throw new Exception($"Incorrect parameter startXcoordinate:[{startXcoordinate}] or endXCoordinate:[{endXCoordinate}],require letter");
        }

        /// <summary>
        /// DishesReportStart
        /// </summary>
        /// 

        //public static string _connectionString = "data source=192.168.0.79;initial catalog=ANKAIS20180309;user id=sa;pwd=123";
        private static string connectionString()
        {
            string connectString="";
            using(StreamReader stream=new StreamReader(AppDomain.CurrentDomain.BaseDirectory + @"\ConnectString.txt"))
            {
                connectString = stream.ReadToEnd();
            }
            return connectString;
        }
        

        public void LSYMethod(DateTime start,DateTime end)
        {
            excelApp.DisplayAlerts = false;            
            Worksheet xcSheetData = (Worksheet)Workbook.Worksheets[1];
            xcSheetData.Name = "下粹报表";

            var xcHeadData = new List<Dictionary<string, string>>();
            //单据体数据
            var xcEntityData = new List<Dictionary<string, string>>();
            LoadUnderPureData(ref xcHeadData, ref xcEntityData,WorkOrder,start,end);
            CreateUnderPureExcel(excelApp, xcSheetData, xcHeadData, xcEntityData);

            //生成测试日报单
            //头部数据
            var testHeadData = new List<Dictionary<string, string>>();
            //单据体数据
            var testEntityData = new List<Dictionary<string, string>>();
        }

        private static void LoadUnderPureData(ref List<Dictionary<string, string>> headData,
            ref List<Dictionary<string, string>> entityData,String workOrder,DateTime start,DateTime end)
        {
            DataSet headDs = new DataSet();
            using (SqlConnection conn = new SqlConnection(connectionString()))
            {
                conn.Open();
                SqlDataAdapter sda = new SqlDataAdapter($"EXEC proc_sy_XCHEADINFO_lsy '{workOrder}','{start}','{end}'", conn);
                sda.Fill(headDs);
                conn.Close();
            }
            foreach (DataRow row in headDs.Tables[0].Rows)
            {
                headData.Add(new Dictionary<string, string>
                {
                    { "型号",row["型号"]?.ToString()??""},
                    { "工单号",row["工单号"]?.ToString()??""},
                    { "来料数量",row["来料数量"]?.ToString()??""},
                    { "下粹数量",row["下粹数量"]?.ToString()??""},
                    { "下粹状态",row["下粹状态"]?.ToString()??""},
                    { "差异比例",row["差异比例"]?.ToString()??""},
                    { "下粹厚度",row["下粹厚度"]?.ToString()??""},
                    { "ABDIE数量",row["ABDIE数量"]?.ToString()??""},
                    { "CDIE数量",row["CDIE数量"]?.ToString()??""}
                });
            }
            //单据体数据
            DataSet entityDs = new DataSet();
            using (SqlConnection conn = new SqlConnection(connectionString()))
            {
                conn.Open();
                SqlDataAdapter sda = new SqlDataAdapter($"exec proc_sy_XCENTITYINFO_lsy '{workOrder}','{start}','{end}'", conn);
                sda.Fill(entityDs);
                conn.Close();
            }
            foreach (DataRow row in entityDs.Tables[0].Rows)
            {
                entityData.Add(new Dictionary<string, string>
                {
                    { "日期",row["日期"]?.ToString()??""},
                    { "来料总箱数",row["来料总箱数"]?.ToString()??""},
                    { "当前箱号行数",row["当前箱号行数"]?.ToString()??""},
                    { "来料箱序号",row["来料箱序号"]?.ToString()??""},
                    { "当前盒行数",row["当前盒行数"]?.ToString()??""},
                    { "来料盒序号",row["来料盒序号"]?.ToString()??""},
                    { "来料包序号",row["来料包序号"]?.ToString()??""},
                    { "来料蓝膜数",row["来料蓝膜数"]?.ToString()??""},
                    { "蓝膜数",row["蓝膜数"]?.ToString()??""},
                    { "来料数量",row["来料数量"]?.ToString()??""},
                    { "下粹厚度",row["下粹厚度"]?.ToString()??""},
                    { "ADIE",row["ADIE"]?.ToString()??""},
                    { "BDIE",row["BDIE"]?.ToString()??""},
                    { "CDIE",row["CDIE"]?.ToString()??""},
                    { "小计",row["小计"]?.ToString()??""},
                    { "差异比例",row["差异比例"]?.ToString()??""}
                });
            }
        }
        /// <summary>
        /// 下粹日报表
        /// </summary>
        /// <param name="application"></param>
        /// <param name="worksheet"></param>
        private static void CreateUnderPureExcel(Application application, Worksheet worksheet, List<Dictionary<string, string>> headData, List<Dictionary<string, string>> entityData)
        {
            //冻结窗口
            //worksheet.Select();
            //application.ActiveWindow.SplitRow = 1;
            //application.ActiveWindow.SplitColumn = 1;
            //application.ActiveWindow.FreezePanes = true;
            //设置全景空白
            worksheet.Cells.Interior.Color = Color.FromArgb(255, 255, 255);
            //标题
            var titleRange = worksheet.Range["B1", "P1"];
            titleRange.Value = "下粹明细表";
            titleRange.Font.Size = 20;
            titleRange.VerticalAlignment = XlVAlign.xlVAlignCenter;
            titleRange.HorizontalAlignment = XlHAlign.xlHAlignCenter;
            //excel的行高单位是磅，行宽单位是像素，1像素=0.75磅
            titleRange.RowHeight = 45;
            titleRange.Font.Color = Color.FromArgb(22, 54, 92);
            titleRange.Merge();
            //汇总区域
            var summaryRangeWidthLetter = 'P';
            if (headData.Count > 11)
            {
                summaryRangeWidthLetter = Convert.ToChar('P' + (headData.Count - 11));
            }
            var summaryRange = worksheet.Range["B2", summaryRangeWidthLetter + "9"];
            summaryRange.Cells.Interior.Color = Color.FromArgb(197, 217, 247);
            //汇总区域边框 只在外部有边框
            var summaryTopRange = worksheet.Range["B2", summaryRangeWidthLetter + "2"];
            summaryTopRange.Borders[XlBordersIndex.xlEdgeTop].Color = Color.FromArgb(141, 180, 246);
            summaryTopRange.Borders[XlBordersIndex.xlEdgeTop].Weight = 4;
            var summaryLeftRange = worksheet.Range["B2", "B9"];
            summaryLeftRange.Borders[XlBordersIndex.xlEdgeLeft].Color = Color.FromArgb(141, 180, 246);
            summaryLeftRange.Borders[XlBordersIndex.xlEdgeLeft].Weight = 4;
            var summaryBottomRange = worksheet.Range["B9", summaryRangeWidthLetter + "9"];
            summaryBottomRange.Borders[XlBordersIndex.xlEdgeBottom].Color = Color.FromArgb(141, 180, 246);
            summaryBottomRange.Borders[XlBordersIndex.xlEdgeBottom].Weight = 4;
            var summaryRightRange = worksheet.Range[summaryRangeWidthLetter + "2", summaryRangeWidthLetter + "9"];
            summaryRightRange.Borders[XlBordersIndex.xlEdgeRight].Color = Color.FromArgb(141, 180, 246);
            summaryRightRange.Borders[XlBordersIndex.xlEdgeRight].Weight = 4;
            //标题单元格集合
            var summaryTitleList = new List<string>
            {
                "C3|D3|型号|true",
                "C4|D4|单号|true",
                "C6|D6|下粹厚度（mm）|true",
                "C7|D7|AB DIE数量（pcs）|true",
                "C8|D8|CDIE数量（pcs）|true",
                "H3|H3|来料数量|true",
                "H4|H4|下粹数量|true",
                "L3|M3|下粹状态|true",
                "L4|M4|差异比例|true",
                "B11|C12|日期|false",
                "D11|D12|来料箱数|false",
                "E11|E12|来料数|false",
                "F11|F12|来料盒数|false",
                "G11|G12|来料蓝膜数|false",
                "H11|H12|蓝膜数|false",
                "I11|I12|来料数量|false",
                "J11|J12|下粹厚度（mm）|false",
                "K11|M11|下片数量|false",
                "K12|K12|A DIE|false",
                "L12|L12|B DIE|false",
                "M12|M12|C DIE|false",
                "N11|N12|小计|false",
                "O11|O12|差异比例|false",
                "P11|P12|汇总|false"
            };
            //值单元格集合
            var summaryValueWhiteList = new List<string>
            {
                "E3|F3|255|255|255",
                "E4|F4|255|255|255",
                "I3|J3|255|255|255",
                "I4|J4|255|255|255",
                "N3|O3|255|255|255",
                "N4|O4|255|255|255",
            };
            //单据头赋值
            if (headData.Count > 1)
            {
                worksheet.Range["E3", "F3"].Value = headData[0]["型号"];
                worksheet.Range["E4", "F4"].Value = headData[0]["工单号"];
                worksheet.Range["I3", "J3"].Value = headData[0]["来料数量"];
                worksheet.Range["I4", "J4"].Value = headData[0]["下粹数量"];
                worksheet.Range["N3", "O3"].Value = headData[0]["下粹状态"];
                worksheet.Range["N4", "O4"].Value = headData[0]["差异比例"];
            }
            var i = 0;
            foreach (var row in headData)
            {
                var titleLetter = Convert.ToChar('E' + i) + "6";
                var valueWhiteLetter = Convert.ToChar('E' + i) + "7";
                var valuePinkLetter = Convert.ToChar('E' + i) + "8";
                summaryTitleList.Add(titleLetter + "|" + titleLetter + "|" + row["下粹厚度"] + "|true");
                worksheet.Range[valueWhiteLetter].Value = row["ABDIE数量"];
                worksheet.Range[valuePinkLetter].Value = row["CDIE数量"];
                summaryValueWhiteList.Add(valueWhiteLetter + "|" + valueWhiteLetter + "|255|255|255");
                summaryValueWhiteList.Add(valuePinkLetter + "|" + valuePinkLetter + "|230|184|183");
                i++;
            }
            //单据体赋值
            var j = 13;
            //当前箱序号
            var currentBoxIndex = -1;
            //当前箱有多少盒
            var currentBoxCase = 0;
            //当前箱数据合并到第几行
            var currentBoxEndRow = 12;
            //当前盒序号
            var currentCaseIndex = 0;
            //当前盒行数
            var currentCaseRows = 0;
            //当前盒数据合并到第几行
            var currentCaseEndRow = 12;
            foreach (var row in entityData)
            {
                if (currentBoxIndex != Convert.ToInt32(row["来料箱序号"]))
                {
                    //自增表示换箱
                    currentBoxIndex = Convert.ToInt32(row["来料箱序号"]);
                    //换箱同时也要换盒
                    currentCaseIndex = 0;
                    currentBoxCase = Convert.ToInt32(row["当前箱号行数"]);
                    currentBoxEndRow += currentBoxCase;
                }
                if (currentCaseIndex != Convert.ToInt32(row["来料盒序号"]))
                {
                    //自增表示换盒
                    currentCaseIndex = Convert.ToInt32(row["来料盒序号"]);
                    currentCaseRows = Convert.ToInt32(row["当前盒行数"]);
                    currentCaseEndRow += currentCaseRows;
                }
                //日期
                var dateRange = worksheet.Range["B" + j, "C" + j];
                dateRange.Merge();
                dateRange.Value = row["日期"].Split(' ')[0];
                //来料总箱数
                var totalBox = worksheet.Range["D13", "D" + (13 + entityData.Count - 1)];
                totalBox.Merge();
                totalBox.Value = row["来料总箱数"];
                //来料数
                var InPackCount =
                    worksheet.Range["E" + (currentBoxEndRow - currentBoxCase + 1), "E" + currentBoxEndRow];
                InPackCount.Merge();
                InPackCount.Formula = "=SUM(I" + (currentBoxEndRow - currentBoxCase + 1) + ":I" +
                                      currentBoxEndRow + ")";
                //来料盒数
                var InPackSeq =
                    worksheet.Range["F" + (currentCaseEndRow - currentCaseRows + 1), "F" + currentCaseEndRow];
                InPackSeq.Merge();
                InPackSeq.Value = row["来料盒序号"];
                worksheet.Range["G" + j].Value = row["来料蓝膜数"];
                worksheet.Range["H" + j].Value = row["蓝膜数"];
                worksheet.Range["I" + j].Value = row["来料数量"];
                worksheet.Range["J" + j].Value = row["下粹厚度"];
                worksheet.Range["K" + j].Value = row["ADIE"];
                worksheet.Range["L" + j].Value = row["BDIE"];
                worksheet.Range["M" + j].Value = row["CDIE"];
                //小计
                worksheet.Range["N" + j].Formula = "=K" + j + "+L" + j;
                worksheet.Range["O" + j].Value = row["差异比例"];
                //汇总
                var summaryCount = worksheet.Range["P" + (currentBoxEndRow - currentBoxCase + 1), "P" + currentBoxEndRow];
                summaryCount.Merge();
                summaryCount.Formula = "=SUM(N" + (currentBoxEndRow - currentBoxCase + 1) + ":N" +
                                       currentBoxEndRow + ")";
                j++;
            }
            //标题单元格样式
            SummaryTitleCellRangeStyle(worksheet, summaryTitleList);
            //内容单元格样式
            SummaryValueCellRangeStyle(worksheet, summaryValueWhiteList);
            //下粹单据体单元格样式
            XcCellRangeStyle(worksheet, entityData.Count);
        }
        private static void CreateMonthlyCapacity(Workbook workbook, Worksheet worksheet)
        {
            Random ran = new Random();
            for (int i = 1; i <= 12; i++)
            {
                worksheet.Cells[i, 1] = i + "月";
                worksheet.Cells[i, 2] = ran.Next(2000).ToString();
            }
            Chart xlChart = (Chart)workbook.Charts.Add(Type.Missing, worksheet, Type.Missing, Type.Missing);

            Range cellRange = (Range)worksheet.Cells[1, 1];
            xlChart.ChartWizard(cellRange.CurrentRegion,
                XlChartType.xl3DColumn, Type.Missing,
                XlRowCol.xlColumns, 1, 0, true,
                "访问量比较(dahuzizyd.cnblogs.com)", "月份", "访问量",
                "");

            xlChart.Name = "统计";

            ChartGroup grp = (ChartGroup)xlChart.ChartGroups(1);
            grp.GapWidth = 20;
            grp.VaryByCategories = true;

            Series s = (Series)grp.SeriesCollection(1);
            s.BarShape = XlBarShape.xlCylinder;
            s.HasDataLabels = true;

            xlChart.Legend.Position = XlLegendPosition.xlLegendPositionTop;
            xlChart.ChartTitle.Font.Size = 24;
            xlChart.ChartTitle.Shadow = true;
            xlChart.ChartTitle.Border.LineStyle = XlLineStyle.xlContinuous;

            Axis valueAxis = (Axis)xlChart.Axes(XlAxisType.xlValue, XlAxisGroup.xlPrimary);
            valueAxis.AxisTitle.Orientation = -90;

            Axis categoryAxis = (Axis)xlChart.Axes(XlAxisType.xlCategory, XlAxisGroup.xlPrimary);
            categoryAxis.AxisTitle.Font.Name = "MS UI Gothic";
        }
        //标题蓝底白字加粗篮框
        private static void SummaryTitleCellRangeStyle(Worksheet worksheet, List<string> list)
        {
            foreach (var l in list)
            {
                var begin = l.Split('|')[0];
                var end = l.Split('|')[1];
                var title = l.Split('|')[2];
                var isBold = Convert.ToBoolean(l.Split('|')[3]);
                var range = worksheet.Range[begin, end];
                range.Merge();
                range.Value = title;
                range.RowHeight = 18;
                range.WrapText = true;
                range.Font.Size = 10;
                range.Font.Bold = isBold;
                range.Font.Name = "Arial";
                range.Font.Color = Color.FromArgb(255, 255, 255);
                range.Borders.Color = Color.FromArgb(197, 217, 247);
                range.Borders.Weight = 3;
                range.Cells.Interior.Color = Color.FromArgb(83, 141, 243);
                range.HorizontalAlignment = XlHAlign.xlHAlignCenter;
                range.VerticalAlignment = XlVAlign.xlVAlignCenter;
            }
        }
        //内容白底蓝框
        private static void SummaryValueCellRangeStyle(Worksheet worksheet, List<string> list)
        {
            foreach (var l in list)
            {
                var begin = l.Split('|')[0];
                var end = l.Split('|')[1];
                var red = Convert.ToInt32(l.Split('|')[2]);
                var green = Convert.ToInt32(l.Split('|')[3]);
                var blur = Convert.ToInt32(l.Split('|')[4]);
                var range = worksheet.Range[begin, end];
                range.Merge();
                range.RowHeight = 18;
                range.Font.Size = 10;
                range.Font.Name = "Arial";
                range.Cells.Interior.Color = Color.FromArgb(red, green, blur);
                range.Borders.Color = Color.FromArgb(197, 217, 247);
                range.Borders.Weight = 3;
                range.HorizontalAlignment = XlHAlign.xlHAlignCenter;
                range.VerticalAlignment = XlVAlign.xlVAlignCenter;
            }
        }
        //下粹单据体数据样式
        private static void XcCellRangeStyle(Worksheet worksheet, int row)
        {
            if (row > 0)
            {
                var begin = "B13";
                var end = "P" + (13 + row - 1);
                worksheet.Range["M13", "M" + (13 + row - 1)].Interior.Color = Color.FromArgb(230, 184, 183);
                var range = worksheet.Range[begin, end];
                range.Font.Color = Color.FromArgb(89, 89, 89);
                range.Font.Name = "Arial";
                range.Font.Size = 10;
                range.Borders.Color = Color.FromArgb(221, 217, 196);
                range.HorizontalAlignment = XlHAlign.xlHAlignCenter;
                range.VerticalAlignment = XlVAlign.xlVAlignCenter;
            }
        }


        /// <summary>
        /// DishesReportEnd
        /// </summary>
    }



    public class Cell
    {
        public int X { get; set; }
        public int Y { get; set; }
        public string Content { get; set; }
        public bool IsBold { get; set; }        

        public Cell(int x, int y, string content="", bool isBold=false)
        {
            X = x;
            Y = y;
            Content = content;
            IsBold = isBold;
        }
        public Color BackColor { get; set; } = Color.White;
        public Color FontColor { get; set; } = Color.Black;
        public Color BorderColor { get; set; } = Color.White;
        public decimal Height { get; set; }
        public decimal Width { get; set; }
        public string  NumberFormat { get; set; }
        public decimal FontSize { get; set; } = 11;
        public bool IsMerge { get; set; }
        public int X2 { get; set; }
        public int Y2 { get; set; }
    }

    public class BINEntryElement
    {
        public BINEntryElement(decimal thickness, decimal qty, string materialClass, string group, DateTime date, string examineType)
        {
            Thickness = thickness;
            Qty = qty;
            MaterialClass = materialClass;
            Group = group;
            Date = date;
            ExamineType = examineType;            
        }

        public decimal Thickness { get; set; }
        public decimal Qty { get; set; }
        public string MaterialClass { get; set; }
        public string Group { get; set; }
        public DateTime Date { get; set; }
        public string ExamineType { get; set; }
       
    }
}
